package com.hethongviet.vending.helper.db;

import java.util.ArrayList;
import java.util.List;

import com.hethongviet.vending.Utils;
import com.hethongviet.vending.model.Box;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class BoxSQLite
{
	public static final String TABLE_NAME = "boxes";

	public static final String COLUMN_ID_DB = "idDB";
	
	public static final String COLUMN_ID = "IDBox";
	public static final String COLUMN_COMMAND = "command";
	public static final String COLUMN_PRODUCT_CODE = "ProductID";
	public static final String COLUMN_PRODUCT_AMOUNT = "product_count";
	public static final String COLUMN_DATE_UPDATED = "date_updated";

	public static final String COLUMN_PRODUCT_PRICE = "Price";
	public static final String COLUMN_NAME = "NameBox";
	public static final String COLUMN_ACTIVE = "Active";

	// Database fields
	private SQLiteDatabase _database;
	private MySQLiteHelper _dbHelper;
	private String[] _allColumns = { COLUMN_ID_DB, 
			
			COLUMN_ACTIVE, COLUMN_COMMAND, COLUMN_ID,

	COLUMN_NAME, COLUMN_PRODUCT_AMOUNT,  COLUMN_DATE_UPDATED, COLUMN_PRODUCT_CODE, COLUMN_PRODUCT_PRICE };
	
	public static final String STRING_COLUMNS = COLUMN_ID_DB
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "

			+ COLUMN_ACTIVE + " integer, " + COLUMN_COMMAND + " text, "
			+ COLUMN_ID + " integer, "

			+ COLUMN_NAME + " text, " + COLUMN_PRODUCT_AMOUNT + " integer, " + COLUMN_DATE_UPDATED + " integer, "
			+ COLUMN_PRODUCT_CODE + " text, " + COLUMN_PRODUCT_PRICE
			+ " integer"

	;

	public BoxSQLite(Context context)
	{
		_dbHelper = new MySQLiteHelper(context, null, null);
	}

	public void open() throws SQLException
	{
		_database = _dbHelper.getWritableDatabase();
	}

	public void close()
	{
		_dbHelper.close();
	}

	private boolean dataExist(Box box, Cursor cursor)
	{
		if (cursor == null)
		{
			return false;
		}
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext())
		{
			long idDB = cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB));
			if (idDB == box.getIdDB()) return true;
		}
		return false;
	}

	/**
	 * @param box
	 * @return row id -1: insert fail
	 */
	public long insert(Box box)
	{
		Utils.debug("insert -- box : " + box.getId());
		open();
		
		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID_DB + " = " + box.getIdDB(), null);

		long temp = -1;
		if (!dataExist(box, cursor))
		{
			Utils.debug("insert 1111111111111 INSERT");
			/* Insert to database */
			/* Insert new */
			Cursor cursorInsert = _database.rawQuery("INSERT INTO "
					+ TABLE_NAME + "("

					+ COLUMN_ACTIVE + ", " + COLUMN_COMMAND + ", " + COLUMN_ID
					+ ", "

					+ COLUMN_NAME + ", " + COLUMN_PRODUCT_AMOUNT + ", " + COLUMN_DATE_UPDATED + ", "
					+ COLUMN_PRODUCT_CODE + ", " + COLUMN_PRODUCT_PRICE

					+ ") values( ?, ?, ?, ?, ?, ?, ?, ?)",

			new String[] { box.getActive() + "", box.getCommand(), box.getId()
					+ "", 
					
					box.getName(), box.getProductAmount()
					+ "", box.getDateUpdated()
					+ "", box.getProductCode(), box.getProductPrice() + "" });

			cursorInsert.moveToFirst();
			if (!cursorInsert.isAfterLast()) temp = cursorInsert.getLong(cursorInsert.getColumnIndex(COLUMN_ID_DB));

			cursorInsert.close();
		}
		else
		{
			Utils.debug("insert 1111111111111 update");
			/* Update to database */
			// Cursor cursorUpdate = _database.rawQuery("UPDATE " + TABLE_NAME
			// + " SET " + COLUMN_CODE + " = ?, " + COLUMN_COMMAND + " = ?, " +
			// COLUMN_PRODUCT_COUNT
			// + " = ?, " + COLUMN_PRODUCT_CODE + " = ? " + "WHERE "
			// + COLUMN_ID_DB + " = " + box.getIdDB(), new String[] {
			// box.getCode(), box.getCommand(), box.getProductAmount()
			// + "", box.getProductCode() });
			// cursorUpdate.moveToFirst();
			// if (!cursorUpdate.isAfterLast()) return
			// cursorUpdate.getLong(cursorUpdate.getColumnIndex(COLUMN_ID_DB));
			//
			// cursorUpdate.close();
			//
			ContentValues contentValues = new ContentValues();
			contentValues.put(COLUMN_ACTIVE, box.getActive());
			contentValues.put(COLUMN_COMMAND, box.getCommand());
			contentValues.put(COLUMN_ID, box.getId());

			contentValues.put(COLUMN_NAME, box.getName());
			contentValues.put(COLUMN_PRODUCT_AMOUNT, box.getProductAmount());
			contentValues.put(COLUMN_DATE_UPDATED, box.getDateUpdated());
			contentValues.put(COLUMN_PRODUCT_CODE, box.getProductCode());
			contentValues.put(COLUMN_PRODUCT_PRICE, box.getProductPrice());

			temp = _database.update(TABLE_NAME, contentValues, COLUMN_ID_DB
					+ " = " + box.getIdDB(), null);
		}
		cursor.close();
		close();
		return temp;
	}

	public void deleteAll()
	{
		open();

		System.out.println("project deleted all");
		// _database.rawQuery("DELETE FROM " + TABLE_NAME, null); not work
		// _database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE 1", null);
		_database.delete(TABLE_NAME, "1", null);
		close();
	}

	public Box getById(int code)
	{
		open();
		Box box = null;
		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID + " = ?", new String[] { code + "" });
		cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{

			box = cursorToBox(cursor);
			break;
		}

		cursor.close();
		close();
		return box;
	}

	public List<Box> getAll()
	{
		open();

		List<Box> listBoxes = new ArrayList<Box>();

		Cursor cursor = _database.query(TABLE_NAME, _allColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			Box box = cursorToBox(cursor);
			listBoxes.add(box);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
		return listBoxes;
	}

	private Box cursorToBox(Cursor cursor)
	{
		Box box = new Box();
		box.setIdDB(cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB)));

		box.setActive(cursor.getInt(cursor.getColumnIndex(COLUMN_ACTIVE)));
		box.setCommand(cursor.getString(cursor.getColumnIndex(COLUMN_COMMAND)));
		box.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));

		box.setName(cursor.getString(cursor.getColumnIndex(COLUMN_NAME)));
		box.setProductAmount(cursor.getInt(cursor.getColumnIndex(COLUMN_PRODUCT_AMOUNT)));
		box.setDateUpdated(cursor.getLong(cursor.getColumnIndex(COLUMN_DATE_UPDATED)));
		box.setProductCode(cursor.getString(cursor.getColumnIndex(COLUMN_PRODUCT_CODE)));
		box.setProductPrice(cursor.getInt(cursor.getColumnIndex(COLUMN_PRODUCT_PRICE)));

		return box;
	}

	public final boolean isTableExist()
	{
		open();

		boolean isExist = false;
		Cursor cursor = _database.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '"
				+ TABLE_NAME + "'", null);
		isExist = cursor != null && cursor.getCount() > 0;

		cursor.close();
		close();

		return isExist;
	}
}
